(ns metabase-enterprise.metabot-v3.tools.util
  (:require
   [clojure.string :as str]
   [medley.core :as m]
   [metabase.api.common :as api]
   [metabase.audit-app.core :as audit-app]
   [metabase.collections.models.collection :as collection]
   [metabase.lib-be.core :as lib-be]
   [metabase.lib.core :as lib]
   [metabase.lib.metadata :as lib.metadata]
   [metabase.lib.types.isa :as lib.types.isa]
   [metabase.lib.util :as lib.util]
   [metabase.premium-features.core :as premium-features]
   [metabase.util :as u]
   [toucan2.core :as t2]))

(defn handle-agent-error
  "Return an agent output for agent errors, re-throw `e` otherwise."
  [e]
  (if (-> e ex-data :agent-error?)
    {:output (ex-message e)}
    (throw e)))

(defn convert-field-type
  "Return tool type for `column`."
  [column]
  (let [column (u/normalize-map column)]
    (cond
      (lib.types.isa/boolean? column)                :boolean
      (lib.types.isa/string-or-string-like? column)  :string
      (lib.types.isa/numeric? column)                :number
      (isa? (:effective-type column) :type/DateTime) :datetime
      (isa? (:effective-type column) :type/Time)     :time
      (lib.types.isa/temporal? column)               :date)))

(defn add-table-reference
  "Add table-reference to columns that have FK relationships."
  [query col]
  (cond-> col
    (and (:fk-field-id col)
         (:table-id col))
    (assoc :table-reference (-> (lib/display-name query (lib.metadata/field query (:fk-field-id col)))
                                lib.util/strip-id))))

(defn table-field-id-prefix
  "Return the field ID prefix for `table-id`."
  [table-id]
  (str "t" table-id "-"))

(defn card-field-id-prefix
  "Return the field ID prefix for a model or a metric with ID `card-id`."
  [card-id]
  (str "c" card-id "-"))

(defn query-field-id-prefix
  "Return the field ID prefix for `query-id`."
  [query-id]
  (str "q" query-id "-"))

(def any-prefix-pattern
  "A prefix pattern accepting columns from any entity."
  #"^.*-(\d+)")

(defn ->result-column
  "Return tool result columns for `column` of `query`. The position of `column` is determined by `index`.
  The ID is then generated by prefixing the position with `field-id-prefix`."
  [query column index field-id-prefix]
  (let [semantic-type (some-> (:semantic-type column) name u/->snake_case_en)]
    (-> {:field_id (str field-id-prefix index)
         ;; Prefer desired-column-alias over source-column-alias to ensure we use deduplicated names if needed
         :name (or (:lib/desired-column-alias column)
                   (:lib/source-column-alias column))
         :display_name (lib/display-name query column)
         :type (convert-field-type column)}
        (m/assoc-some :description (:description column)
                      :database_type (:database-type column)
                      :semantic_type semantic-type
                      :field_values (:field-values column)
                      :table_reference (:table-reference column)))))

(defn parse-field-id
  "Parse a field-id string into its components.

  The field-id format is '<model-tag><model-id>-<field-index>' where:
  - model-tag is 't' for tables, 'c' for cards/models/metrics, or 'q' for ad-hoc queries
  - model-id is the numeric ID (for tables/cards) or nano-id (for queries)
  - field-index is the index within that model's visible columns

  Returns a map with :model-tag, :model-id, and :field-index keys, or nil if the format is invalid.

  Examples:
    (parse-field-id \"t154-1\") => {:model-tag \"t\", :model-id 154, :field-index 1}
    (parse-field-id \"qpuL95JSvym3k23W1UUuog-0\") => {:model-tag \"q\", :model-id \"puL95JSvym3k23W1UUuog\", :field-index 0}"
  [field-id]
  (when-let [[_ model-tag model-id field-index] (re-matches #"^([tcq])(.+)-(\d+)$" field-id)]
    {:model-tag model-tag
     ;; For tables and cards, model-id should be numeric; for queries it's a nano-id string
     :model-id (if (= model-tag "q")
                 model-id
                 (parse-long model-id))
     :field-index (parse-long field-index)}))

(defn resolve-column
  "Resolve the reference `field-id` in filter `item` by finding the column in `columns` specified by `field-id`.

  The field-id format is '<model-tag><model-id>-<field-index>' where:
  - model-tag is 't' for tables, 'c' for cards/models/metrics, or 'q' for ad-hoc queries
  - model-id is the numeric ID (for tables/cards) or nano-id (for queries)
  - field-index is the index within the columns array (using wide field IDs across all visible columns)

  The `expected-prefix` parameter validates that the field-id starts with the expected prefix (e.g., 't154-' for table 154).
  This prevents accidentally using a field-id from a different entity.

  For example, 't154-1' refers to the column at index 1 in the columns array,
  and 'qpuL95JSvym3k23W1UUuog-0' refers to the column at index 0."
  [{:keys [field-id] :as item} expected-prefix columns]
  (if-let [{:keys [model-tag model-id field-index]} (parse-field-id field-id)]
    (do
      ;; Validate that the field-id matches the expected prefix
      ;; Supports both string prefixes (e.g., "t154-") and regex patterns (e.g., #"^.*-(\d+)")
      (when-not (if (string? expected-prefix)
                  (str/starts-with? field-id expected-prefix)
                  (re-matches expected-prefix field-id))
        (throw (ex-info (str "field " field-id " does not match expected prefix " expected-prefix)
                        {:agent-error? true
                         :field-id field-id
                         :expected-prefix expected-prefix})))
      (if-let [column (get columns field-index)]
        (assoc item :column column)
        (throw (ex-info (str "field " field-id " not found - no column at index " field-index)
                        {:agent-error? true
                         :field-id field-id
                         :model-tag model-tag
                         :model-id model-id
                         :field-index field-index
                         :available-columns-count (count columns)}))))
    (throw (ex-info (str "invalid field_id format: " field-id)
                    {:agent-error? true
                     :field-id field-id}))))

(defn get-database
  "Get the `fields` of the database with ID `id`."
  [id & fields]
  (-> (t2/select-one (into [:model/Database :id] fields) id)
      api/read-check))

(defn get-table
  "Get the `fields` of the table with ID `id`."
  [id & fields]
  (-> (t2/select-one (into [:model/Table :id] fields) id)
      api/read-check))

(defn get-card
  "Retrieve the card with `id` from the app DB."
  [id]
  (-> (t2/select-one :model/Card :id id)
      api/read-check))

(defn card-query
  "Return a query based on the model with ID `model-id`."
  [card-id]
  (when-let [card (get-card card-id)]
    (let [mp (lib-be/application-database-metadata-provider (:database_id card))]
      (lib/query mp (cond-> (lib.metadata/card mp card-id)
                      ;; pivot questions have strange result-columns so we work with the dataset-query
                      (#{:question} (:type card)) (get :dataset-query))))))

(defn metric-query
  "Return a query based on the model with ID `model-id`."
  [metric-id]
  (when-let [card (get-card metric-id)]
    (let [mp (lib-be/application-database-metadata-provider (:database_id card))]
      (lib/query mp (lib.metadata/metric mp metric-id)))))

(defn table-query
  "Return a query based on the table with ID `table-id`."
  [table-id]
  (when-let [table (get-table table-id :db_id)]
    (let [mp (lib-be/application-database-metadata-provider (:db_id table))]
      (lib/query mp (lib.metadata/table mp table-id)))))

(defn metabot-metrics-and-models-query
  "Return the metric and model cards in metabot scope visible to the current user.

  Takes a metabot-id and returns all metric and model cards in that metabot's collection
  and its subcollections. If the metabot has use_verified_content enabled, only verified
  content is returned.

  Ignores analytics content."
  [metabot-id & {:keys [limit] :as _opts}]
  (let [metabot (t2/select-one :model/Metabot :id metabot-id)
        metabot-collection-id (:collection_id metabot)
        use-verified-content? (:use_verified_content metabot)
        collection-filter (if metabot-collection-id
                            (let [collection (t2/select-one :model/Collection :id metabot-collection-id)
                                  collection-ids (conj (collection/descendant-ids collection) metabot-collection-id)]
                              [:in :collection_id collection-ids])
                            [:and true])
        base-query {:select [:report_card.*]
                    :from   [[:report_card]]
                    :where [:and
                            [:!= :report_card.database_id audit-app/audit-db-id]
                            collection-filter
                            [:in :type [:inline ["metric" "model"]]]
                            [:= :archived false]
                            (when api/*current-user-id*
                              (collection/visible-collection-filter-clause :collection_id))]}]
    (cond-> base-query

      ;; Prioritize verified content.
      (premium-features/has-feature? :content-verification)
      (assoc
       :left-join [[:moderation_review :mr] [:and
                                             [:= :mr.moderated_item_id :report_card.id]
                                             [:= :mr.moderated_item_type [:inline "card"]]
                                             [:= :mr.most_recent true]]]
       :order-by [[[:case [:= :mr.status [:inline "verified"]] [:inline 0]
                    :else [:inline 1]]
                   :asc]])

      ;; Filter verified items only when that's desired.
      (and (premium-features/has-feature? :content-verification)
           use-verified-content?)
      (update :where conj [:= :mr.status [:inline "verified"]])

      (integer? limit)
      (assoc :limit limit))))

(comment
  (binding [api/*current-user-id* 2
            api/*is-superuser?* true]
    (t2/select-fn-vec #(select-keys % [:id :name :type])
                      :model/Card
                      (metabot-metrics-and-models-query 1)))
  -)

(defn get-metrics-and-models
  "Retrieve the metric and model cards for the Metabot instance with ID `metabot-id` from the app DB.

  Only cards visible to the current user are returned."
  [metabot-id & {:as opts}]
  (t2/select :model/Card (-> (metabot-metrics-and-models-query metabot-id opts)
                             (update :order-by (fnil conj []) [:id]))))
